#include<stdio.h>
#include<sqlite3.h>
#include<string.h>
#include<stdlib.h>
//helloWorld
#define NAME_MAX 16
#define SQ_CMD_MAX 64
char *errmsg;

int create_table(sqlite3* data);

int show_table(sqlite3 *data);

int insert_item(sqlite3 *data);

int callback(void *para,int f_num,char **f_val,char ** f_name);

int show_table_v2(sqlite3 *data);

int delete_item(sqlite3 *);

int main(int argc, const char *argv[])
{
    sqlite3 *data;
    int result;

    if((result = sqlite3_open("my.db",&data)) != SQLITE_OK){
        printf("error :%s\n",errmsg);
    }else{
        printf("open sucess :%d\n",result);
    }
   
    int input;
    while(1){
        puts("");
        puts("");
        puts("************************************");
        puts("*1.create a table 2.insert a item  *");
        puts("*3.delete a item  4.show the table *");
        puts("*5.quit                            *");
        puts("************************************");
        puts("");
        puts("");
   
        puts("please input a number (1 - 5):");
        scanf("%d",&input);

        switch(input){
        case 1:
            if(!create_table(data)){
                puts("create table success!");
            }
            break;
        case 2:
            if(!insert_item(data)){
                puts("insert item success!");
            }
            break;
        case 3:
            delete_item(data);
            break;
        case 4:
        //     show_table(data);
             show_table_v2(data);
            break;
        case 5:
            exit(0);
        default:
            puts("please choose a number (1 - 5)  again");
        }
    }
    
    return 0;
}


int create_table(sqlite3* data){
    char    table_name [NAME_MAX] = {0};
    char    sqlcmd     [SQ_CMD_MAX] = {0};
    int     result;

    puts("input the table's name");
    scanf("%s",table_name);

    sprintf(sqlcmd,"create table %s (id integer primary key,name text,age integer)",table_name);

    if((result = sqlite3_exec(data,sqlcmd,NULL,NULL,&errmsg)) != SQLITE_OK){
        printf("error : %s\n",errmsg); 
    }
    return result;
}

int insert_item(sqlite3 *data){
    int     id;
    char    name[NAME_MAX] = {0};
    int     age;
    char    sqlcmd[SQ_CMD_MAX] = {0};
    char    table_name[NAME_MAX] = {0};
    int     result;
    

    puts("input:table_name id name age");
    scanf("%s %d %s %d",table_name,&id,name,&age);

    sprintf(sqlcmd,"insert into %s values(%d,'%s',%d)",table_name,id,name,age);
    puts(sqlcmd);

    if((result = sqlite3_exec(data,sqlcmd,NULL,NULL,&errmsg)) != SQLITE_OK){
        printf("error :%s\n",errmsg);
    }
    return result;
}
int show_table(sqlite3 *data){

    char    sqlcmd[SQ_CMD_MAX] = {0};
    int     result;
    char    table_name[NAME_MAX] = {0};

    
    puts("table name :");
    scanf("%s",table_name);
    
    sprintf(sqlcmd,"select * from %s",table_name);

    if((result = sqlite3_exec(data,sqlcmd,callback,NULL,&errmsg)) !=SQLITE_OK){
        printf("error:%s\n",errmsg);
    }
    return result;
}

int callback(void *para,int f_num,char **f_val,char ** f_name){
    int i;
    puts("***********************");
    for(i = 0;i < f_num;i++){
        printf("%-8s ",f_name[i]);
    }
    putchar(10);
    for(i = 0;i < f_num;i++){
        printf("%-8s ",f_val[i]);
    }
    putchar(10);
    puts("***********************");
    return 0;
}

int show_table_v2(sqlite3 *data){
    char    table_name[NAME_MAX] = {0};
    char    sqlcmd[SQ_CMD_MAX] = {0};
    int     nrow = 0;
    int     ncolumn = 0;
    int     i,j;
    char ** result;

    puts("input the table name:");
    scanf("%s",table_name);

    sprintf(sqlcmd,"select * from %s",table_name);

    puts(sqlcmd);

    if(sqlite3_get_table(data,sqlcmd,&result,&nrow,&ncolumn,&errmsg) != 0){
        printf("error:%s\n",errmsg);
        exit(0);
    } 


    for(i = 0;i < nrow;i++){
        for(j = 0;j < ncolumn;j++){
            printf("%5s ",result[i * ncolumn + j]);
        }
        putchar(10);
    }
    return 0;

}

int delete_item(sqlite3* data){
    
    char    table_name[NAME_MAX] = {0};
    char    sqlcmd[SQ_CMD_MAX] = {0};
    int     id;
    int     result;

    printf("input the item that you want delete:");
    printf("input like this :(table_name id)");
    scanf("%s %d",table_name,&id);

    sprintf(sqlcmd,"delete from %s where id = %d",table_name,id);
    puts(sqlcmd);

    if((result = sqlite3_exec(data,sqlcmd,NULL,NULL,&errmsg)) != SQLITE_OK){
        printf("error :%s\n",errmsg);
    }

    return result;
}
